package com.kcsj.dao.User;
//201902104058胡庆阳

import com.kcsj.domain.Count;
import com.kcsj.domain.user.Role;
import com.kcsj.domain.user.User;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

/**
 * @author 胡庆阳/傅立贤
 * 29th Nov. 2020
 */
public final class UserDao {
    private static final UserDao userDao = new UserDao();
    private UserDao() {
    }

    public static UserDao getInstance() {
        return userDao;
    }
    //查询所有的user
    public Collection<User> findAll(Connection conn, List<Condition> conditionList) throws SQLException {
        Collection<User> users = new LinkedList<>();
        StringBuilder s = new StringBuilder("select * from user");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            s.append(whereClause);
        }
        PreparedStatement preparedStatement = conn.prepareStatement(s.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String password = resultSet.getString(3);
            String type = resultSet.getString(4);
            int role_id = resultSet.getInt(5);
            Timestamp timestamp = resultSet.getTimestamp(6);
            Boolean activationStatus = resultSet.getBoolean(7);
            Role role = RoleDao.getInstance().find(role_id, conn);
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            User user = new User(id, username, password,timestamp.toLocalDateTime().format(dtf),type,role, activationStatus);
            users.add(user);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return users;
    }
    //查询一个user
    public User find(Integer id, Connection conn) throws SQLException {
        User user = null;
        //预编译方式进行查询
        String s = "select * from user where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()) {
            String username = resultSet.getString(2);
            String password = resultSet.getString(3);
            String type = resultSet.getString(4);
            int role_id = resultSet.getInt(5);
            Timestamp timestamp = resultSet.getTimestamp(6);
            Boolean activationStatus = resultSet.getBoolean(7);
            Role role = RoleDao.getInstance().find(role_id, conn);
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            user = new User(id, username,password,timestamp.toLocalDateTime().format(dtf),type,role, activationStatus);
        }
        JdbcHelper.close(preparedStatement);
        return user;
    }


    //更新user
    public boolean update(User user,Connection conn) throws SQLException {
        //预编译方式进行查询
        String s = "update user set username = ?,password = ?,type = ?,role_id=?,activationStatus = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,user.getUsername());
        preparedStatement.setString(2,user.getPassword());
        preparedStatement.setString(3,user.getType());
        preparedStatement.setInt(4,user.getRole().getId());
        preparedStatement.setBoolean(5,user.getActivationStatus());
        preparedStatement.setInt(6,user.getId());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return i>0;
    }
    //添加user
    public boolean add(User user, Connection connection) throws SQLException {
        String spl = "CALL addUser(?,?,?,?,?)";
        CallableStatement callableStatement = connection.prepareCall(spl);
        callableStatement.setString(1,user.getUsername());
        callableStatement.setString(2,user.getPassword());
        callableStatement.setString(3,user.getType());
        callableStatement.setInt(4,user.getRole().getId());
        callableStatement.setBoolean(5,user.getActivationStatus());
        int rowsAffected = callableStatement.executeUpdate();
        JdbcHelper.close(callableStatement);
        return rowsAffected>0;
    }

    //激活
    public void activeByCode(String code,Connection conn) throws SQLException {
        //根据激活码查找账号将帐号状态改变，并将激活码滞空，表示激活码只可用一次
        String sql = "update user set activationStatus=1,code=null where code=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,code);
        pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
    }

    //根据code查找
    public User findByCode(String code,Connection conn) throws SQLException {
        //根据激活码查找账号
        String sql = "select *  from user where code=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,code);
        ResultSet resultSet = pstmt.executeQuery();
        User user = null;
        if(resultSet.next()){
            int user_id = resultSet.getInt("id");
            user = new User();
            user.setId(user_id);
        }
        JdbcHelper.close(pstmt);
        return user;
    }
    //删除user
    public boolean delete(Integer id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from user where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
    //登入
    public User login(Connection connection,String username) throws SQLException {
        //创建Teacher类型的引用，暂不创建对象
        String sql_login = "SELECT * FROM user WHERE username = ?";
        PreparedStatement pstmt_login =
                connection.prepareStatement(sql_login);
        pstmt_login.setString(1, username);
        ResultSet resultSet_login = pstmt_login.executeQuery();
        //如果表中存在id对应的记录，则获得表中的actor_id，并获得对应的Actor对象
        User user = null;
        if (resultSet_login.next()) {
            int id = resultSet_login.getInt(1);
            String password = resultSet_login.getString(3);
            boolean activationStatus = resultSet_login.getBoolean(7);
            user = new User(id,username,password,activationStatus);
        }
        return user;
    }


    //查询一个user
    public ArrayList<Count> countByRoleId(Connection conn) throws SQLException {
        ArrayList<Count> counts = new ArrayList<>();
        //预编译方式进行查询
        String s = "SELECT role.id,name,COUNT(user.role_id) AS number FROM `role` LEFT JOIN  `user` on user.role_id = role.id GROUP BY role.id";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String name = resultSet.getString(2);
            int value = resultSet.getInt(3);
            Count count = new Count(name, value);
            counts.add(count);
        }
        JdbcHelper.close(preparedStatement);
        return counts;
    }

    //查询一个user
    public ArrayList<Count> count(String sort,Connection conn) throws SQLException {
        ArrayList<Count> counts = new ArrayList<>();
        //预编译方式进行查询
        String s = "SELECT "+sort+",COUNT(*) AS number FROM `user` GROUP BY "+sort;
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String name = resultSet.getString(1);
            int value = resultSet.getInt(2);
            Count count = new Count(name, value);
            counts.add(count);
        }
        JdbcHelper.close(preparedStatement);
        return counts;
    }

}
